package com.ijmacd.footballtracker;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URI;
import java.net.URL;
import java.util.HashMap;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;

import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.graphics.Bitmap.CompressFormat;
import android.net.Uri;
import android.os.AsyncTask;
import android.provider.BaseColumns;
import android.text.TextUtils;
import android.util.Log;

/**
 * Provides access to a database of notes. Each note has a title, the note
 * itself, a creation date and a modified data.
 */
public class Provider extends ContentProvider {

    private static final String TAG = "Provider";
    
    public static final String AUTHORITY = "com.ijmacd.footballtracker";

	public static final String PREFS_NAME = "DataPrefs";
	public static final String PREFKEY_TEAMS_UPDATED = "teams_updated";
	public static final String PREFKEY_MATCHES_UPDATED = "matches_updated";

    private static final String MATCHES_TABLE_NAME = "matches";
    private static final String TEAMS_TABLE_NAME = "teams";
    private static final String EXTRA_TABLE_NAME = "extra";

    private static HashMap<String, String> sMatchesProjectionMap;
    private static HashMap<String, String> sTeamsProjectionMap;

    private static final int MATCHES = 0;
    private static final int MATCH_ID = 2;
    private static final int TEAMS = 1;
    private static final int TEAM_ID = 3;

        public static final int STATUS_DUE				= 0;	//000
        public static final int STATUS_PLAYING			= 1;	//001
        public static final int STATUS_HALFTIME			= 2;	//010
        public static final int STATUS_PLAYED			= 4;	//100 == Not being played anymore
        public static final int STATUS_POSTPONED		= 5;	//101
        public static final int STATUS_ABANDONED		= 6;	//110
    
    private static final UriMatcher sUriMatcher;

	/**
     * This class helps open, create, and upgrade the database file.
     */
    private class DatabaseHelper extends SQLiteOpenHelper {
    	
    	private static final String TAG = "DatabaseHelper";
        private static final String DATABASE_NAME = "footballtracker.db";
        private static final int DATABASE_VERSION = 1;
        private static final String MATCHES_TABLE_NAME = "matches";
        private static final String TEAMS_TABLE_NAME = "teams";
        private static final String EXTRA_TABLE_NAME = "extra";
        
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + MATCHES_TABLE_NAME + " ("
                    //+ Matches._ID + " INTEGER PRIMARY KEY,"
                    + Matches._ID + " TEXT PRIMARY KEY,"
                    + Matches.COMPETITION + " TEXT,"
                    + Matches.DATE + " DATETIME,"
                    + Matches.HOME_TEAM + " TEXT,"
                    + Matches.HOME_GOALS + " INTEGER,"
                    + Matches.AWAY_TEAM + " TEXT,"
                    + Matches.AWAY_GOALS + " INTEGER,"
                    + Matches.STATUS + " INTEGER"
                    + ");");
            db.execSQL("CREATE TABLE " + TEAMS_TABLE_NAME + " ("
                    + Teams._ID + " TEXT PRIMARY KEY,"
                    + Teams.NAME + " TEXT"
                    + ");");
            db.execSQL("CREATE TABLE " + EXTRA_TABLE_NAME + " ("
                    + Extra._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + Extra.UUID + " TEXT,"
                    + Extra.NAME + " TEXT,"
                    + Extra.VALUE + " TEXT"
                    //+ "UNIQUE " + Extra.UUID + "," + Extra.NAME
                    + ");");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
        
        public Uri insertMatch(ContentValues values){
        	// Make sure that the fields are all set

            boolean success = true;
            success &= values.containsKey(Matches._ID);
            //success &= values.containsKey(Matches.COMPETITION);
            success &= values.containsKey(Matches.DATE);
            success &= values.containsKey(Matches.HOME_TEAM);
            success &= values.containsKey(Matches.AWAY_TEAM);

            if(!success) return null;

            if (values.containsKey(Matches.HOME_GOALS) == false) {
                values.put(Matches.HOME_GOALS, 0);
            }

            if (values.containsKey(Matches.AWAY_GOALS) == false) {
                values.put(Matches.AWAY_GOALS, 0);
            }

            if (values.containsKey(Matches.STATUS) == false) {
                values.put(Matches.STATUS, STATUS_PLAYED);
            }else{
            	if(values.get(Matches.STATUS).getClass() == String.class)
            		values.put(Matches.STATUS, parseStatus(values.getAsString(Matches.STATUS)));
            }

            if(values.containsKey(Matches.ATTENDANCE)){
            	insertExtra(values.getAsString(Matches._ID), Matches.ATTENDANCE, values.getAsString(Matches.ATTENDANCE));
            	values.remove(Matches.ATTENDANCE);
            }
            
            SQLiteDatabase db = getWritableDatabase();
            Cursor c = db.query(MATCHES_TABLE_NAME, new String[] {Matches._ID}, Matches._ID + "='" + values.get(Matches._ID) + "'", null, null, null, null);
            if(c.getCount() > 0){
            	db.update(MATCHES_TABLE_NAME, values, Matches._ID + "='" + values.get(Matches._ID) + "'", null);
            }else{
    	        long rowId = db.insert(MATCHES_TABLE_NAME, Matches._ID, values);
    	        if (rowId > 0) {
    	            return Uri.parse(Matches.CONTENT_URI + "/" + values.get(Matches._ID));
    	        }
            }
            c.close();
            c = null;

    		return null;
        }


        public Uri insertTeam(ContentValues values) {
        	// Make sure that the fields are all set
            if(!values.containsKey(Teams._ID) || 
            	!values.containsKey(Teams.NAME)) return null;

            if(values.containsKey(Teams.LOGO_SMALL)){
            	insertExtra(values.getAsString(Teams._ID), Teams.LOGO_SMALL, values.getAsString(Teams.LOGO_SMALL));
            	values.remove(Teams.LOGO_SMALL);
            }

            SQLiteDatabase db = getWritableDatabase();
            Cursor c = db.query(TEAMS_TABLE_NAME, new String[] {Teams._ID}, Teams._ID + "='" + values.get(Teams._ID) + "'", null, null, null, null);
            if(c.getCount() > 0){
            	db.update(TEAMS_TABLE_NAME, values, Teams._ID + "='" + values.get(Teams._ID) + "'", null);
            }else{
    		    long rowId = db.insert(TEAMS_TABLE_NAME, Teams._ID, values);
    	        if (rowId > 0) {
    	            return Uri.parse(Teams.CONTENT_URI + "/" + values.get(Teams._ID));
    	        }
            }
            c.close();
            c = null;

    		return null;
    	}

    	public void insertExtra(String uuid, String name, String value) {
    		ContentValues values = new ContentValues();
    		values.put(Extra.UUID, uuid);
    		values.put(Extra.NAME, name);
    		values.put(Extra.VALUE, value);
    		Log.d(TAG, "insertingExtra: " + uuid + " - " + name + " -> " + value);
            SQLiteDatabase db = getWritableDatabase();
            Cursor c = db.query(EXTRA_TABLE_NAME, new String[] {Extra._ID}, Extra.UUID + "='" + values.get(Extra.UUID) + "' AND " + Extra.NAME + "='" + values.get(Extra.NAME) + "'", null, null, null, null);
            if(c.getCount() > 0){
            	c.moveToFirst();
            	db.update(EXTRA_TABLE_NAME, values, Extra._ID + "=" + c.getInt(0), null);
            }else{
    		    db.insert(EXTRA_TABLE_NAME, Extra.UUID, values);
            }
            c.close();
    	}
        
    	public int parseStatus(String status) {
    		if(status.equals("DUE")) 			return STATUS_DUE;
    		if(status.equals("PLAYING")) 		return STATUS_PLAYING;
    		if(status.equals("HALFTIME")) 		return STATUS_HALFTIME;
    		if(status.equals("PLAYED")) 		return STATUS_PLAYED;
    		if(status.equals("POSTPONED"))		return STATUS_POSTPONED;
    		if(status.equals("ABANDONED"))		return STATUS_ABANDONED;
    		return STATUS_PLAYED;
    	}
    }
    private DatabaseHelper mOpenHelper;

    @Override
    public boolean onCreate() {
        mOpenHelper = new DatabaseHelper(getContext());
        return true;
    }

    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
            String sortOrder) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        Log.i(TAG, "URI Queried: " + uri);

        switch (sUriMatcher.match(uri)) {
        case MATCHES:
            qb.setTables(MATCHES_TABLE_NAME);
            qb.setProjectionMap(sMatchesProjectionMap);
            if(uri.getQueryParameter("team") != null){
                qb.appendWhere(Matches.HOME_TEAM + "='" + uri.getQueryParameter("team") + "'");
                qb.appendWhere(" OR ");
                qb.appendWhere(Matches.AWAY_TEAM + "='" + uri.getQueryParameter("team") + "'");
            }else if(uri.getQueryParameter("home_team") != null){
                qb.appendWhere(Matches.HOME_TEAM + "='" + uri.getQueryParameter("home_team") + "'");
            }else if(uri.getQueryParameter("away_team") != null){
            	qb.appendWhere(Matches.AWAY_TEAM + "='" + uri.getQueryParameter("away_team") + "'");
            }
        	//new RefreshMatchesTask().execute("");
            break;

        case MATCH_ID:
            qb.setTables(MATCHES_TABLE_NAME);
            qb.setProjectionMap(sMatchesProjectionMap);
            qb.appendWhere(Matches._ID + "='" + uri.getPathSegments().get(1) + "'");
            break;
            
        case TEAMS:
            qb.setTables(TEAMS_TABLE_NAME);
            qb.setProjectionMap(sTeamsProjectionMap);
        	//new RefreshTeamsTask().execute("");
            break;

        case TEAM_ID:
            qb.setTables(TEAMS_TABLE_NAME);
            qb.setProjectionMap(sTeamsProjectionMap);
            qb.appendWhere(Teams._ID + "='" + uri.getPathSegments().get(1) + "'");
            break;

        default:
            throw new IllegalArgumentException("Unknown URI " + uri);
        }

        // If no sort order is specified use the default
        String orderBy = "";
        if (TextUtils.isEmpty(sortOrder)) {

            switch (sUriMatcher.match(uri)) {
            case MATCHES:
            case MATCH_ID:
	            orderBy = Matches.DEFAULT_SORT_ORDER;
	            break;
            case TEAMS:
            case TEAM_ID:
	            orderBy = Teams.DEFAULT_SORT_ORDER;
	            break;
            }
        } else {
            orderBy = sortOrder;
        }

        // Get the database and run the query
        SQLiteDatabase db = mOpenHelper.getReadableDatabase();
        Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, orderBy);

        // Tell the cursor what uri to watch, so it knows when its source data changes
        c.setNotificationUri(getContext().getContentResolver(), uri);

    	
        return c;
    }

    @Override
    public String getType(Uri uri) {
        switch (sUriMatcher.match(uri)) {
        case MATCHES:
            return Matches.CONTENT_TYPE;

        case MATCH_ID:
            return Matches.CONTENT_ITEM_TYPE;
            
        case TEAMS:
            return Teams.CONTENT_TYPE;

        case TEAM_ID:
            return Teams.CONTENT_ITEM_TYPE;

        default:
            throw new IllegalArgumentException("Unknown URI " + uri);
        }
    }

    @Override
    public Uri insert(Uri uri, ContentValues initialValues) {

        Uri resultUri;
		// Validate the requested uri
        switch(sUriMatcher.match(uri)){
        case MATCHES:
        	resultUri = mOpenHelper.insertMatch(initialValues);
        	break;
        case TEAMS:
        	resultUri = mOpenHelper.insertTeam(initialValues);
        	break;
        default:
        	throw new IllegalArgumentException("Unknown URI " + uri);
        }
        
        if(resultUri == null) throw new SQLException("Failed to insert row into " + uri);

        getContext().getContentResolver().notifyChange(resultUri, null);
		return resultUri;
    }

	@Override
	public int bulkInsert(Uri uri, ContentValues[] values) {

		int added = 0;
		// Validate the requested uri
        switch(sUriMatcher.match(uri)){
        case MATCHES:
        	for(int i = 0; i < values.length; i++){
        		if(mOpenHelper.insertMatch(values[i]) != null) added++;
        	}
        	break;
        case TEAMS:
        	for(int i = 0; i < values.length; i++){
        		if(mOpenHelper.insertTeam(values[i]) != null) added++;
        	}
        	break;
        default:
        	throw new IllegalArgumentException("Unknown URI " + uri);
        }
        Log.i(TAG, "Added " + added + " rows");
        getContext().getContentResolver().notifyChange(uri, null);
		return added;
	}

	@Override
    public int delete(Uri uri, String where, String[] whereArgs) {
        SQLiteDatabase db = mOpenHelper.getWritableDatabase();
        int count;
        switch (sUriMatcher.match(uri)) {
        case MATCHES:
            count = db.delete(MATCHES_TABLE_NAME, where, whereArgs);
            break;

        case MATCH_ID:
            String match_uuid = uri.getPathSegments().get(1);
            count = db.delete(MATCHES_TABLE_NAME, Matches._ID + "='" + match_uuid + "'"
                    + (!TextUtils.isEmpty(where) ? " AND (" + where + ')' : ""), whereArgs);
            break;

        case TEAMS:
            count = db.delete(TEAMS_TABLE_NAME, where, whereArgs);
            break;

        case TEAM_ID:
            String team_uuid = uri.getPathSegments().get(1);
            count = db.delete(TEAMS_TABLE_NAME, Teams._ID + "='" + team_uuid + "'"
                    + (!TextUtils.isEmpty(where) ? " AND (" + where + ')' : ""), whereArgs);
            break;

        default:
            throw new IllegalArgumentException("Unknown URI " + uri);
        }

        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }

    @Override
    public int update(Uri uri, ContentValues values, String where, String[] whereArgs) {
        SQLiteDatabase db = mOpenHelper.getWritableDatabase();
        int count;
        switch (sUriMatcher.match(uri)) {
        case MATCHES:
            count = db.update(MATCHES_TABLE_NAME, values, where, whereArgs);
            break;

        case MATCH_ID:
            String match_uuid = uri.getPathSegments().get(1);
            count = db.update(MATCHES_TABLE_NAME, values, Matches._ID + "='" + match_uuid + "'"
                    + (!TextUtils.isEmpty(where) ? " AND (" + where + ')' : ""), whereArgs);
            break;
        case TEAMS:
            count = db.update(TEAMS_TABLE_NAME, values, where, whereArgs);
            break;

        case TEAM_ID:
            String team_uuid = uri.getPathSegments().get(1);
            count = db.update(TEAMS_TABLE_NAME, values, Teams._ID + "='" + team_uuid + "'"
                    + (!TextUtils.isEmpty(where) ? " AND (" + where + ')' : ""), whereArgs);
            break;

        default:
            throw new IllegalArgumentException("Unknown URI " + uri);
        }

        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }
    

	public class RefreshMatchesTask extends AsyncTask<String, Integer, Integer> {
		
		static final String QUERY_URL = "http://footballtracker.co.cc/query.php?request=match&limit=1000";
				
		@Override
		protected Integer doInBackground(String... params) {
			Log.d(TAG, "Downloading matches");
            SharedPreferences settings = getContext().getSharedPreferences(PREFS_NAME, 0);
	        Document doc = null;
	        
	        try{
	        	String uri = QUERY_URL + "&since=" + settings.getInt(PREFKEY_MATCHES_UPDATED, 0);
	        	Log.d(TAG, "URI: " + uri);
	        	doc = new SAXBuilder().build(URI.create(uri).toURL());

	        	if(doc.getRootElement().getChild("query") != null){
		        	Element result = doc.getRootElement().getChild("query").getChild("result");
		        	if(result != null){
		        		int count = result.getAttribute("count").getIntValue();
		        		int num = result.getChildren("match").size();
		        		Log.d(TAG, count + " matches reported and " + num + " found");
		        		if(count != num){
		        			Log.w(TAG, "The reported number of results does not match the number found");
		        		}
		        		if(num > 0){
					        ContentValues[] values = new ContentValues[num];
				        	int i = 0;
				        	for(Object match_o : result.getChildren("match")){
				        		if(match_o instanceof Element){
				        			Element match = (Element) match_o;
				                    values[i] = new ContentValues();
				                    String uuid = match.getAttributeValue("uuid");
				                    if(uuid.substring(0, 9).equals("urn:uuid:")) uuid = uuid.substring(9);
				                    values[i].put(Matches._ID, uuid);
				                    if(match.getChild("home") != null){
				                    	if(match.getChild("home").getChild("team") != null){
					                    	String home_uuid = match.getChild("home").getChild("team").getAttributeValue("uuid");
						                    if(home_uuid.substring(0, 9).equals("urn:uuid:")) home_uuid = home_uuid.substring(9);
						                    values[i].put(Matches.HOME_TEAM, home_uuid);
				                    	}
					                    values[i].put(Matches.HOME_GOALS, match.getChild("home").getChildText("goals"));
				                    }
				                    if(match.getChild("away") != null){
				                    	if(match.getChild("away").getChild("team") != null){
					                    	String away_uuid = match.getChild("away").getChild("team").getAttributeValue("uuid");
						                    if(away_uuid.substring(0, 9).equals("urn:uuid:")) away_uuid = away_uuid.substring(9);
						                    values[i].put(Matches.AWAY_TEAM, away_uuid);
				                    	}
					                    values[i].put(Matches.AWAY_GOALS, match.getChild("away").getChildText("goals"));
				                    }
				                    values[i].put(Matches.DATE, match.getChildText("date"));
				                    values[i].put(Matches.STATUS, mOpenHelper.parseStatus(match.getChildText("status")));
				                    if(match.getChildText("attendance") != null){
				                    	//mOpenHelper.insertExtra(uuid, "attendance", match.getChildText("attendance"));
				                    }
				                    i++;
				        		}
				        	}
				        	bulkInsert(Matches.CONTENT_URI, values);
		        		}
		        	}
	        	}
	        }catch (IOException ioe) {
	        	//Toast.makeText(TeamListActivity.this, "Invalid XML Format\n" + ioe.getMessage(), Toast.LENGTH_LONG).show();
	        	Log.e(TAG + " IOException", "Error: " + ioe.getMessage());
	        	ioe.printStackTrace();
	        } catch (RuntimeException re) {
	        	//Toast.makeText(TeamListActivity.this, "XML Format Error\n" + re.getMessage(), Toast.LENGTH_LONG).show();
	        	Log.e(TAG + " RuntimeException", "Error: " + re.getMessage());
	        	re.printStackTrace();
	        } catch (Exception ex){
	        	//Toast.makeText(this, "Real error\n" + ex.getMessage(), Toast.LENGTH_LONG).show();
	        	Log.e(TAG + " Exception", "Real Error: " + ex.getMessage());
	        }
			return null;
		}

	}

	public class RefreshTeamsTask extends AsyncTask<String, Integer, Integer> {
		
		static final String QUERY_URL = "http://footballtracker.co.cc/query.php?request=team&limit=1000";
		
		@Override
		protected Integer doInBackground(String... params) {
			Log.d(TAG, "Downloading teams");
            SharedPreferences settings = getContext().getSharedPreferences(PREFS_NAME, 0);
	        Document doc = null;
	        
	        try{
	        	String uri = QUERY_URL + "&since=" + settings.getInt(PREFKEY_TEAMS_UPDATED, 0);
	        	Log.d(TAG, "URI: " + uri);
	        	doc = new SAXBuilder().build(URI.create(uri).toURL());

	        	if(doc.getRootElement().getChild("query") != null){
		        	Element result = doc.getRootElement().getChild("query").getChild("result");
		        	if(result != null){
		        		int count = result.getAttribute("count").getIntValue();
		        		int num = result.getChildren("team").size();
		        		Log.d(TAG, count + " teams reported and " + num + " found");
		        		if(count != num){
		        			Log.w(TAG, "The reported number of results does not match the number found");
		        		}
		        		if(num > 0){
					        ContentValues[] values = new ContentValues[num];
				        	int i = 0;
				        	for(Object team_o : result.getChildren("team")){
				        		if(team_o instanceof Element){
				        			Element team = (Element) team_o;
				                    values[i] = new ContentValues();
				                    String uuid = team.getAttributeValue("uuid");
				                    if(uuid.substring(0, 9).equals("urn:uuid:")) uuid = uuid.substring(9);
				                    values[i].put(Teams._ID, uuid);
				                    values[i].put(Teams.NAME, team.getChildText("name"));
				                    if(team.getChildText("logo_small") != null){
				                    	URL url = new URL(team.getChildText("logo_small"));
				                    	File file = new File("/sdcard/footballtracker.co.cc/cache/logo_small/" + uuid);
				        				file.getParentFile().mkdirs();
				        				if(file.getParentFile().exists() && !file.exists()){
					                    	Bitmap bitmap = BitmapFactory.decodeStream(url.openStream());
					                    	bitmap.compress(CompressFormat.PNG, 100, new FileOutputStream(file));
					                    	//mOpenHelper.insertExtra(uuid, "logo_small", file.getAbsolutePath());
					                    }
				                    }
				                    i++;
				        		}
				        	}
				        	bulkInsert(Teams.CONTENT_URI, values);
				        	Element request = doc.getRootElement().getChild("query").getChild("request");
				        	int time = request.getAttribute("timestamp").getIntValue();
				            SharedPreferences.Editor editor = settings.edit();
				            editor.putInt(PREFKEY_TEAMS_UPDATED, time);
				            editor.commit();
		        		}
		        	}
	        	}
		        }catch (IOException ioe) {
		        	//Toast.makeText(TeamListActivity.this, "Invalid XML Format\n" + ioe.getMessage(), Toast.LENGTH_LONG).show();
		        	Log.e(TAG + " IOException", "Error: " + ioe.getMessage());
		        	ioe.printStackTrace();
		        } catch (RuntimeException re) {
		        	//Toast.makeText(TeamListActivity.this, "XML Format Error\n" + re.getMessage(), Toast.LENGTH_LONG).show();
		        	Log.e(TAG + " RuntimeException", "Error: " + re.getMessage());
		        	re.printStackTrace();
		        } catch (Exception ex){
		        	//Toast.makeText(this, "Real error\n" + ex.getMessage(), Toast.LENGTH_LONG).show();
		        	Log.e(TAG + " Exception", "Real Error: " + ex.getMessage());
		        }
				return null;
			}
	}
	
    static {
        sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
        sUriMatcher.addURI(AUTHORITY, "matches", MATCHES);
        sUriMatcher.addURI(AUTHORITY, "matches/*", MATCH_ID);
        sUriMatcher.addURI(AUTHORITY, "teams", TEAMS);
        sUriMatcher.addURI(AUTHORITY, "teams/*", TEAM_ID);


        sMatchesProjectionMap = new HashMap<String, String>();
        sMatchesProjectionMap.put(Matches._ID, Matches._ID);
        sMatchesProjectionMap.put(Matches.COMPETITION, Matches.COMPETITION);
        sMatchesProjectionMap.put(Matches.DATE, Matches.DATE);
        sMatchesProjectionMap.put(Matches.HOME_TEAM, Matches.HOME_TEAM);
        sMatchesProjectionMap.put(Matches.HOME_TEAM_NAME, "(SELECT "+ Teams.NAME +" FROM " + TEAMS_TABLE_NAME + " WHERE " + Teams._ID + " = " + Matches.HOME_TEAM + ") AS " + Matches.HOME_TEAM_NAME);
        sMatchesProjectionMap.put(Matches.HOME_GOALS, Matches.HOME_GOALS);
        sMatchesProjectionMap.put(Matches.AWAY_TEAM, Matches.AWAY_TEAM);
        sMatchesProjectionMap.put(Matches.AWAY_TEAM_NAME, "(SELECT "+ Teams.NAME +" FROM " + TEAMS_TABLE_NAME + " WHERE " + Teams._ID + " = " + Matches.AWAY_TEAM + ") AS " + Matches.AWAY_TEAM_NAME);
        sMatchesProjectionMap.put(Matches.AWAY_GOALS, Matches.AWAY_GOALS);
        sMatchesProjectionMap.put(Matches.STATUS, Matches.STATUS);
        sMatchesProjectionMap.put(Matches.ATTENDANCE, "(SELECT + " + Extra.VALUE + " FROM " + EXTRA_TABLE_NAME + " WHERE " + Extra.UUID + " = " + MATCHES_TABLE_NAME + "." + Matches._ID + " AND " + Extra.NAME + " = 'attendance') AS " + Matches.ATTENDANCE);

        sTeamsProjectionMap = new HashMap<String, String>();
        sTeamsProjectionMap.put(Teams._ID, Teams._ID);
        sTeamsProjectionMap.put(Teams.NAME, Teams.NAME);
        sTeamsProjectionMap.put(Teams.LOGO_SMALL, "(SELECT + " + Extra.VALUE + " FROM " + EXTRA_TABLE_NAME + " WHERE " + Extra.UUID + " = " + TEAMS_TABLE_NAME + "." + Teams._ID + " AND " + Extra.NAME + " = 'logo_small') AS " + Teams.LOGO_SMALL);
        
    }
    

    	public static final class Matches implements BaseColumns {

			// This class cannot be instantiated
            private Matches() {}

            /**
             * The content:// style URL for this table
             */
            public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/matches");

            /**
             * The MIME type of {@link #CONTENT_URI} providing a directory of notes.
             */
            public static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.ijmacd.footballtracker.match";

            /**
             * The MIME type of a {@link #CONTENT_URI} sub-directory of a single note.
             */
            public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/vnd.ijmacd.footballtracker.match";

            /**
             * The uuid - IS NOW ._ID
             * <P>Type: TEXT</P>
             */
            //public static final String UUID = "uuid";
            /**
             * The date of the match
             * <P>Type: TEXT</P>
             */
            public static final String DATE = "date";
            /**
             * The competition
             * <P>Type: TEXT</P>
             */
            public static final String COMPETITION = "competition_uuid";
            /**
             * The home team
             * <P>Type: TEXT</P>
             */
            public static final String HOME_TEAM = "home_uuid";
            /**
             * The home team
             * <P>Type: TEXT</P>
             */
            public static final String HOME_TEAM_NAME = "home_team";
            /**
             * The home goals
             * <P>Type: TEXT</P>
             */
            public static final String HOME_GOALS = "home_goals";

            /**
             * The away team
             * <P>Type: TEXT</P>
             */
            public static final String AWAY_TEAM = "away_uuid";
            /**
             * The away team
             * <P>Type: TEXT</P>
             */
            public static final String AWAY_TEAM_NAME = "away_team";

            /**
             * The away goals
             * <P>Type: TEXT</P>
             */
            public static final String AWAY_GOALS = "away_goals";

            /**
             * The status of the match
             * <P>Type: TEXT</P>
             */
            public static final String STATUS = "status";
            
            public static final String ATTENDANCE = "attendance";
            

            /**
             * The default sort order for this table
             */
            public static final String DEFAULT_SORT_ORDER = "date DESC, " + Matches.HOME_TEAM_NAME + " ASC";
        }
    	
    	public static final class Teams implements BaseColumns {
            // This class cannot be instantiated
            private Teams() {}

            /**
             * The content:// style URL for this table
             */
            public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/teams");

            /**
             * The MIME type of {@link #CONTENT_URI} providing a directory of teams.
             */
            public static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.ijmacd.footballtracker.team";

            /**
             * The MIME type of a {@link #CONTENT_URI} sub-directory of a single team.
             */
            public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/vnd.ijmacd.footballtracker.team";

            /**
             * The default sort order for this table
             */
            public static final String DEFAULT_SORT_ORDER = "name ASC";
            
            /**
             * The team uuid
             * <P>Type: TEXT</P>
             */
            //public static final String UUID = "uuid";
            /**
             * The team name
             * <P>Type: TEXT</P>
             */
            public static final String NAME = "name";
            public static final String LOGO_SMALL = "logo_small";
        }

    	private static final class Extra implements BaseColumns {
            // This class cannot be instantiated
            private Extra() {}
            public static final String UUID = "uuid";
            public static final String NAME = "name";
            public static final String VALUE = "value";
    	}
}
